﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Server.Models;
using System.Data.SqlClient;
using Server.Util;
using System.Data;

namespace Server.BaseService
{
    public class BaseManageDAL
    {
        DBUtil dbCtrl = new DBUtil();
        #region 商品管理
        public void CommoidtyAdd(CommodityModel cm)
        {
            try
            {
                string sqlstr = " insert into Client_Commodity(uid,CName,ParentCode,Code,SimpCode,Models,Unit,TaxCode,Discount,TaxItem,Tax,Price,TaxMark,TaxName,DiscountCate) values(@uid,@CName,@ParentCode,@Code,@SimpCode,@Models,@Unit,@TaxCode,@Discount,@TaxItem,@Tax,@Price,@TaxMark,@TaxName,@DiscountCate) ";
                SqlParameter[] op = new SqlParameter[]
                {
                    new SqlParameter {SqlDbType = SqlDbType.Int,ParameterName = "@uid",Value = cm.uid},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@CName",Value = cm.CName},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@ParentCode",Value = cm.ParentCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Code",Value = cm.Code},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@SimpCode",Value = cm.SimpCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Models",Value = cm.Models},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Unit",Value = cm.Unit},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@TaxCode",Value = cm.TaxCode},
                    new SqlParameter {SqlDbType = SqlDbType.Int,ParameterName = "@Discount",Value = cm.Discount},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@TaxItem",Value = cm.TaxItem},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Tax",Value = cm.Tax},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Price",Value = cm.Price},
                    new SqlParameter {SqlDbType = SqlDbType.Int,ParameterName = "@TaxMark",Value = cm.TaxMark},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@TaxName",Value = cm.TaxName},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@DiscountCate",Value = cm.DiscountCate},
                };
                dbCtrl.Exec(sqlstr, op);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public void CommoidtyEdit(CommodityModel cm)
        {
            try
            {
                string sqlstr = "    update Client_Commodity set CName = @CName,ParentCode = @ParentCode,Code = @Code,SimpCode = @SimpCode,Models = @Models,Unit = @Unit,TaxItem = @TaxItem,Tax = @Tax,Price = @Price,TaxMark = @TaxMark,TaxName = @TaxName,DiscountCate = @DiscountCate where id = @id ";
                SqlParameter[] op = new SqlParameter[]
                {
                    new SqlParameter{SqlDbType = SqlDbType.VarChar,ParameterName = "@CName",Value = cm.CName},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@ParentCode",Value = cm.ParentCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Code",Value = cm.Code},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@SimpCode",Value = cm.SimpCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Models",Value = cm.Models},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Unit",Value = cm.Unit},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@TaxCode",Value = cm.TaxCode},
                    new SqlParameter {SqlDbType = SqlDbType.Int,ParameterName = "@Discount",Value = cm.Discount},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@TaxItem",Value = cm.TaxItem},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Tax",Value = cm.Tax},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Price",Value = cm.Price},
                    new SqlParameter {SqlDbType = SqlDbType.Int,ParameterName = "@TaxMark",Value = cm.TaxMark},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@TaxName",Value = cm.TaxName},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@DiscountCate",Value = cm.DiscountCate},
                    new SqlParameter {SqlDbType = SqlDbType.Int,ParameterName = "@id",Value = cm.id},
                };
                dbCtrl.Exec(sqlstr, op);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public void CommoidtyDel(int id)
        {
            try
            {
                string sqlstr = " delect from Client_Commodity where id =  " + id;
                dbCtrl.Exec(sqlstr);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public DataTable GetCommidityByPage(string PageNum)
        {
            try
            {
                string sqlstr = "";
                if ("1" == PageNum)
                    sqlstr = " SELECT TOP 10 * FROM Client_Commodity ORDER BY ID";
                else
                    sqlstr = " SELECT TOP 10 * FROM Client_Commodity WHERE(ID NOT IN   (SELECT TOP(10 * (" + PageNum + " - 1)) id  FROM Client_Commodity   ORDER BY id)) ORDER BY ID";
                var dt = dbCtrl.Query(sqlstr);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public DataTable GetCommidityByLikeName(string LikeName, int uid)
        {
            try
            {
                string sqlstr = string.Format(" select * from Client_Commodity where ( CName like '%{0}%' or TaxCode like '%{0}%' ) and uid = {1} ", LikeName, uid);
                var dt = dbCtrl.Query(sqlstr);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 客户管理
        public void CustomerAdd(CustomerModel cm)
        {
            try
            {
                string sqlstr = " insert into Client_Customer_Public(ParentCode,Code,SimpCode,CName,TaxCode,Email,Address_Tel,Bank_Number,Remark) values(@ParentCode,@Code,@SimpCode,@CName,@TaxCode,@Email,@Address_Tel,@Bank_Number,@Remark) SELECT @@Identity ";
                SqlParameter[] op = new SqlParameter[]
                {
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@ParentCode",Value = cm.ParentCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Code",Value = cm.Code},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@SimpCode",Value = cm.SimpCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@CName",Value = cm.CName},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@TaxCode",Value = cm.TaxCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Email",Value = cm.Email},
                    new SqlParameter {SqlDbType = SqlDbType.Int,ParameterName = "@Address_Tel",Value = cm.Address_Tel},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Bank_Number",Value = cm.Bank_Number},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Remark",Value = cm.Remark},
                };
                var dt = dbCtrl.Query(sqlstr, op);
                sqlstr = " insert into Client_Customer_Private(Pubid,uid,ParentCode,Code,SimpCode,CName,TaxCode,Email,Address_Tel,Bank_Number,Remark) values(@Pubid,@uid,@ParentCode,@Code,@SimpCode,@CName,@TaxCode,@Email,@Address_Tel,@Bank_Number,@Remark) ";
                op = new SqlParameter[]
                {
                    new SqlParameter {SqlDbType = SqlDbType.Int,ParameterName = "@Pubid",Value = dt.Rows[0][0]},
                    new SqlParameter {SqlDbType = SqlDbType.Int,ParameterName = "@uid",Value = cm.uid},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@ParentCode",Value = cm.ParentCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Code",Value = cm.Code},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@SimpCode",Value = cm.SimpCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@CName",Value = cm.CName},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@TaxCode",Value = cm.TaxCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Email",Value = cm.Email},
                    new SqlParameter {SqlDbType = SqlDbType.Int,ParameterName = "@Address_Tel",Value = cm.Address_Tel},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Bank_Number",Value = cm.Bank_Number},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Remark",Value = cm.Remark}
                };
                dbCtrl.Exec(sqlstr, op);

            }
            catch (Exception)
            {
                throw;
            }
        }
        public void CustomerEdit(CustomerModel cm)
        {
            try
            {
                string sqlstr = " update Client_Customer_Public set ParentCode = @ParentCode,Code = @Code,SimpCode = @SimpCode,CName = @CName,TaxCode = @TaxCode,Email = @Email,Address_Tel = @Address_Tel,Bank_Number = @Bank_Number,Remark = @Remark where id = @id ";
                SqlParameter[] op = new SqlParameter[]
                {
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@ParentCode",Value = cm.ParentCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Code",Value = cm.Code},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@SimpCode",Value = cm.SimpCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@CName",Value = cm.CName},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@TaxCode",Value = cm.TaxCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Email",Value = cm.Email},
                    new SqlParameter {SqlDbType = SqlDbType.Int,ParameterName = "@Address_Tel",Value = cm.Address_Tel},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Bank_Number",Value = cm.Bank_Number},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Remark",Value = cm.Remark},
                    new SqlParameter {SqlDbType = SqlDbType.Int,ParameterName = "@id",Value = cm.id},
                };
                dbCtrl.Exec(sqlstr,op);

                sqlstr = " update Client_Customer_Private set ParentCode = @ParentCode,Code = @Code,SimpCode = @SimpCode,CName = @CName,TaxCode = @TaxCode,Email = @Email,Address_Tel = @Address_Tel,Bank_Number = @Bank_Number,Remark = @Remark where Pubid = @Pubid ";
                op = new SqlParameter[]
                {
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@ParentCode",Value = cm.ParentCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Code",Value = cm.Code},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@SimpCode",Value = cm.SimpCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@CName",Value = cm.CName},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@TaxCode",Value = cm.TaxCode},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Email",Value = cm.Email},
                    new SqlParameter {SqlDbType = SqlDbType.Int,ParameterName = "@Address_Tel",Value = cm.Address_Tel},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Bank_Number",Value = cm.Bank_Number},
                    new SqlParameter {SqlDbType = SqlDbType.VarChar,ParameterName = "@Remark",Value = cm.Remark},
                    new SqlParameter {SqlDbType = SqlDbType.Int,ParameterName = "@Pubid",Value = cm.id},
                };
                dbCtrl.Exec(sqlstr,op);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public void CustomerDel(int id)
        {
            try
            {
                string sqlstr = " delete from Client_Customer_Public where id =  " + id;
                dbCtrl.Exec(sqlstr);
                sqlstr = " delete from Client_Customer_Private where Pubid =  " + id;
                dbCtrl.Exec(sqlstr);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public DataTable GetCustomerByPage(string PageNum)
        {
            try
            {
                string sqlstr = "";
                if ("1" == PageNum)
                    sqlstr = " SELECT TOP 10 * FROM Client_Customer_Public ORDER BY ID";
                else
                    sqlstr = " SELECT TOP 10 * FROM Client_Customer_Public WHERE(ID NOT IN   (SELECT TOP(10 * (" + PageNum + " - 1)) id  FROM Client_Customer_Public ORDER BY id)) ORDER BY ID";
                var dt = dbCtrl.Query(sqlstr);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public DataTable GetCustomerByLikeName(string LikeName, int uid)
        {
            try
            {
                string sqlstr = string.Format(" select * from Client_Customer_Public where ( CName like '%{0}%' or TaxCode like '%{0}%' ) and uid = {1} ", LikeName, uid);
                var dt = dbCtrl.Query(sqlstr);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 获取客户编号
        /// </summary>
        /// <returns></returns>
        public string GetCode()
        {
            try
            {
                string sqlstr = "    select max(convert(int, Code)) from  Client_Customer_Public  ";
                var dt = dbCtrl.Query(sqlstr);
                return dt.Rows[0][0].ToString();
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion
        #region 获取广告
        public AdsManageModel[] GetAdsList()
        {
            try
            {
                string sqlstr = " select * from Manage_Ads ";
                var dt = dbCtrl.Query(sqlstr);
                AdsManageModel[] ads = new AdsManageModel[dt.Rows.Count];
                for (int i = 0; i < ads.Length; i++)
                {
                    var dr = dt.Rows[i];
                    ads[i] = new AdsManageModel()
                    {
                        AIndex = Convert.ToInt32( dr["AIndex"]),
                        PictureUrl = dr["PictureUrl"].ToString(),
                        NetUrl = dr["NetUrl"].ToString(),
                    };
                }
                return ads;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

    }
}
